
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.CRM.BigCustomerFreightConfigExtention;
using BCCommon.TMS.Order;
using BCCommon.TMS.FreightEnums;

namespace BCData.CRM.BigCustomerFreightConfigExtention
{
    public class BigCustomerFreightConfigExtentionData : IBigCustomerFreightConfigExtentionData
    {
        private readonly IDatabaseContext database;
        private readonly string QUERY = @"SELECT
		    A.BigCustomerFreightConfigExtentionId,
		    A.FreightConfigurationExtentionId,
		    A.CompanyId,
		    A.AgentRecordId,
		    A.BigCustomerId,
		    A.FirstWeight,
		    A.MaxWeight,
		    A.StartPrice,
		    A.EveryWeightPrice,
            A.MileagePrice,
            A.SameCountyMaxMileage,
            A.SameCountyRatio,
		    A.SameCityMaxMileage,
		    A.SameCityRatio,
		    A.SameProvinceMileage,
		    A.SameProvinceRatio,
		    A.CreateTime,
		    A.CreateAdminId,
		    A.CreateAdminName,
		    A.UpdateTime,
		    A.EditAdminId,
		    A.EditAdminName
        FROM
            BigCustomerFreightConfigExtention A";

        private readonly string QueryView = @"
            SELECT
             {0}
            FROM
            freightconfigurationextention A
            LEFT JOIN province P ON A.ProvinceCode = P.ProvinceCode
            LEFT JOIN city C ON A.CityCode = C.CityCode
            LEFT JOIN county CO ON A.CountyCode = CO.CountyCode 
            INNER JOIN vehicletype B ON A.VehicleTypeId = B.VehicleTypeId
            LEFT JOIN (
            SELECT
            A.vehiclecategoryreferenceId,
            A.VehicleTypeId,
            A.VehicleCategory,
            B.ItemValue AS CategoryValue,
            C.VehicleTypeName
            FROM
            vehiclecategoryreference A
            INNER JOIN dictionaryreference B ON A.VehicleCategory = B.SearchKey
            INNER JOIN vehicletype C ON A.VehicleTypeId = C.VehicleTypeId
            ) D ON A.VehicleCategoryReferenceId = D.VehicleCategoryReferenceId 
            INNER JOIN bigcustomerfreightconfigextention E ON E.BigCustomerFreightConfigExtentionId = A.FreightConfigurationExtentionId";

        private readonly string QueryViewFiled = @"
		    E.BigCustomerFreightConfigExtentionId,
		    E.FreightConfigurationExtentionId,
		    E.CompanyId,
		    E.AgentRecordId,
		    E.BigCustomerId,
		    E.FirstWeight,
		    E.MaxWeight,
		    E.StartPrice,
		    E.EveryWeightPrice,
            E.MileagePrice,
            E.SameCountyMaxMileage,
            E.SameCountyRatio,
		    E.SameCityMaxMileage,
		    E.SameCityRatio,
		    E.SameProvinceMileage,
		    E.SameProvinceRatio,
		    E.CreateTime,
		    E.CreateAdminId,
		    E.CreateAdminName,
		    E.UpdateTime,
		    E.EditAdminId,
		    E.EditAdminName,
            A.ProvinceCode,
            A.CityCode,
            A.CountyCode,
            A.TransportMode,
            A.TransportModeStr,
            A.SourceType,
            A.FreightType,
            A.VehicleTypeId,
            A.VehicleCategoryReferenceId,
            CO.CountyName,
            P.ProvinceName,
            C.CityName,
            B.VehicleTypeName,
            D.CategoryValue
";

        #region 构造函数
        public BigCustomerFreightConfigExtentionData(IDatabaseContext database)
        {
            this.database = database;
        }
        #endregion

        #region InsertAsync
        public async Task<BigCustomerFreightConfigExtentionEntity> InsertAsync(BigCustomerFreightConfigExtentionEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }
        #endregion

        #region updateAsync
        public async Task<BigCustomerFreightConfigExtentionEntity> UpdateAsync(BigCustomerFreightConfigExtentionEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }
        #endregion 

        #region Delete
        public async Task<bool> DeleteAsync(long bigCustomerFreightConfigExtentionId)
        {
            string sql = "DELETE FROM bigcustomerfreightconfigextention WHERE BigCustomerFreightConfigExtentionId = ?bigCustomerFreightConfigExtentionId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BigCustomerFreightConfigExtentionId", bigCustomerFreightConfigExtentionId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction) > 0;
        }
        #endregion

        #region GetById
        public async Task<BigCustomerFreightConfigExtentionEntity> GetEntityAsync(long bigCustomerFreightConfigExtentionId)
        {
            string sql = QUERY + " WHERE  A.BigCustomerFreightConfigExtentionId = ?bigCustomerFreightConfigExtentionId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BigCustomerFreightConfigExtentionId", bigCustomerFreightConfigExtentionId, DbType.Int64);
            return await this.database.Slave.QueryEntityAsync<BigCustomerFreightConfigExtentionEntity>(sql, parameters);
        }
        #endregion



        #region GetEntityAsync
        public async Task<BigCustomerFreightConfigExtentionEntity> GetEntityAsync(long freightConfigExtentionId, long bigCustomerId)
        {
            DynamicParameters parameters = new DynamicParameters();
            List<string> wheres = new List<string>();
            wheres.Add("FreightConfigurationExtentionId=?FreightConfigurationExtentionId");
            parameters.Add("?FreightConfigurationExtentionId", freightConfigExtentionId, DbType.Int64);
            wheres.Add("BigCustomerId=?BigCustomerId");
            parameters.Add("?BigCustomerId", bigCustomerId, DbType.Int64);

            string sql = string.Format("{0} {1}",
                QUERY,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");
            return await this.database.Slave.QueryEntityAsync<BigCustomerFreightConfigExtentionEntity>(sql, parameters);
        }
        #endregion



        #region GetListAsync 查询大客户运费配置扩展记录
        public async Task<(IEnumerable<BigCustomerFreightConfigExtentionViewEntity>, int)> GetListAsync(int? companyId, long? agentRecordId, long? bigCustomerId, int? vehicleTypeId, int? vehicleCategoryReferenceId, string ProvinceCode, string cityCode, string countyCode, OrderSourceType? sourceType, FreightType? freightType, int pageIndex, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();
            if (companyId.HasValue)
            {
                wheres.Add("CompanyId=?CompanyId");
                parameters.Add("?CompanyId", companyId, DbType.Int64);
            }
            if (agentRecordId.HasValue)
            {
                wheres.Add("AgentRecordId=?AgentRecordId");
                parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
            }
            if (bigCustomerId.HasValue)
            {
                wheres.Add("BigCustomerId=?BigCustomerId");
                parameters.Add("?BigCustomerId", bigCustomerId, DbType.Int64);
            }
            if (vehicleTypeId.HasValue)
            {
                wheres.Add(" A.VehicleTypeId=?VehicleTypeId");
                parameters.Add("?VehicleTypeId", vehicleTypeId);
            }
            if (vehicleCategoryReferenceId.HasValue)
            {
                wheres.Add(" A.VehicleCategoryReferenceId=?VehicleCategoryReferenceId");
                parameters.Add("?VehicleCategoryReferenceId", vehicleCategoryReferenceId);
            }
            if (!string.IsNullOrWhiteSpace(ProvinceCode))
            {
                wheres.Add("A.ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", ProvinceCode);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("A.CityCode=?CityCode");
                parameters.Add("?CityCode", cityCode);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode=?CountyCode");
                parameters.Add("?CountyCode", countyCode);
            }
            if (sourceType.HasValue)
            {
                wheres.Add("A.SourceType = ?SourceType");
                parameters.Add("?SourceType", sourceType.Value.ToString(), DbType.AnsiString);
            }
            if (freightType.HasValue)
            {
                wheres.Add("A.FreightType = ?FreightType");
                parameters.Add("?FreightType", freightType.Value.ToString(), DbType.AnsiString);
            }

            string sql = string.Format(@"{0} {1} LIMIT {2},{3}; {4}  {1}",
                string.Format(QueryView, QueryViewFiled),
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                string.Format(QueryView, "COUNT(1)"));


            return await this.database.Slave.QueryPageableListAsync<BigCustomerFreightConfigExtentionViewEntity>(sql, parameters);
        }
        #endregion

    }
}
        //接口
        
        
        
